package tsli.acm.dao;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class SFCCommissionNegativeDAO {
	private Database db;
    

	 public SFCCommissionNegativeDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
		 String vaSql = "SELECT * "
				+ "FROM TSLI_SFC_COMMISSION_NEGATIVE";
		 List<Map<String, Object>> result = db.queryList(vaSql);   
		 if (result != null && result.size() > 0) {
			 return result;
		 } else {
			 return null;
		 }
	 }
	 
	 public String[][] findFYPForCalRA(String paDate) {
			String vaSql = "SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_negative " 
					+ "WHERE MONTHS_BETWEEN (TO_DATE(?, 'YYYYMM'), TO_DATE(CLOS_YM, 'YYYYMM')) between 0 and 5 " 
					+ "GROUP BY clos_ym, pe_no_col, po_no";
			List<Map<String, Object>> voResult = db.queryList(vaSql, paDate);
	    	String[][] vaResult = null;
	    	if (voResult != null) {
	    		vaResult = new String[voResult.size()][4];
	    		for (int i = 0; i < voResult.size(); i++) {
	    			Map<String, Object> voTmp = voResult.get(i);
	    			vaResult[i][0] = (String) voTmp.get("CLOS_YM");
	    			vaResult[i][1] = ((BigDecimal) voTmp.get("SUM_FYP")).toString();
	    			vaResult[i][2] = (String) voTmp.get("PO_NO");
	    			vaResult[i][3] = (String) voTmp.get("PE_NO_COL");
	    		}
	            return vaResult;
	        } else {
	            return null;
	        }
		}
	 
	 
	 
	 public String getFreeLookTotal(String vaPeNo, String vaClosYm){
			String vaSql = "SELECT CASE"
					+ "		          WHEN fst_enco_mms = '1' THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '2' THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '3' AND PERSISTENCY_RATE >= 98 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '4' AND PERSISTENCY_RATE >= 97 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '5' AND PERSISTENCY_RATE >= 96 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '6' AND PERSISTENCY_RATE >= 95 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '7' AND PERSISTENCY_RATE >= 94 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '8' AND PERSISTENCY_RATE >= 93 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '9' AND PERSISTENCY_RATE >= 92 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '10' AND PERSISTENCY_RATE >= 91 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '11' AND PERSISTENCY_RATE >= 90 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          WHEN fst_enco_mms = '12' AND PERSISTENCY_RATE >= 89 THEN "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN FA_FREE_LOOK ELSE 0 END"
					+ "		          ELSE "
					+ "		          CASE WHEN bizrt_itm_data = 'Y' THEN (FA_FREE_LOOK*0.90) ELSE 0 END"
					+ "		          END AS FA_TOTAL"
					+ "		FROM(SELECT CASE "
					+ "		               WHEN commission.FREE_LOOK < 10000 THEN 0"
					+ "		               WHEN commission.FREE_LOOK < 20000 THEN 2500"
					+ "		               WHEN commission.FREE_LOOK < 30000 THEN 5000"
					+ "		               WHEN commission.FREE_LOOK < 40000 THEN 7500"
					+ "		               WHEN commission.FREE_LOOK < 50000 THEN 10500"
					+ "		               WHEN commission.FREE_LOOK < 70000 THEN 13500"
					+ "		               ELSE 19000 END FA_FREE_LOOK,"
					+ "		            agent_structure.fst_enco_mms,"
					+ "		            tsli_standard_activity.bizrt_itm_data,"
					+ "		            persistency.PERSISTENCY_RATE"
					+ "		      FROM(SELECT p.PE_NO_COL,NVL(SUM(n.afyp+n.aryp2),0) AS FREE_LOOK"
					+ "		            FROM TSLI_SFC_COMMISSION_POSITIVE p LEFT JOIN TSLI_SFC_COMMISSION_NEGATIVE  n ON p.pe_no_col = n.pe_no_col "
					+ "		            WHERE p.po_no=n.po_no AND p.rider_cd = n.rider_cd AND n.clos_ym = ? AND p.PE_NO_COL = ?"
					+ "		            GROUP BY p.PE_NO_COL) commission"
					+ "		            LEFT JOIN tsli_sfc_agent_structure  agent_structure  ON commission.PE_NO_COL = agent_structure.PE_NO"
					+ "		            JOIN (select bizrt_itm_data , bizrt_itm_amt , PE_NO from tsli_standard_activity) tsli_standard_activity ON tsli_standard_activity.PE_NO = commission.PE_NO_COL  "
					+ "		            JOIN (SELECT CASE WHEN SUM(PRST_BIZRT) IS NULL OR SUM(SOLI_BIZRT) IS NULL THEN NULL ELSE NVL(CAST(((SUM(PRST_BIZRT) / SUM(SOLI_BIZRT)) * 100) AS Decimal(20,2)),0) END AS PERSISTENCY_RATE , PE_NO FROM TSLI_PERSISTENCY group by PE_NO) persistency ON persistency.PE_NO = commission.PE_NO_COL)";
			Map<String, Object> result = db.querySingle(vaSql,vaClosYm,vaPeNo);
			if (result != null) {
				return result.get("FA_TOTAL").toString().trim();
			} else {
				return "0.00";
			}
		}

	public String findFeeLookInMount(String paPeNoMM, String paClosYm) {
		String vaSql = "SELECT count(*) AS FEE_LOOK_AMOUNT "
				+ " FROM tsli_sfc_commission_negative  commission_negative "
				+ " WHERE  commission_negative.PE_NO_MM = ? AND commission_negative.clos_ym = ?";
		Map<String, Object> result = db.querySingle(vaSql, paPeNoMM,paClosYm);
		if ( result != null){
			return result.get("FEE_LOOK_AMOUNT").toString();
		} else {
			return "";
		}
	}

	public String findFeeLookPcOfMM(String vaPeNoMM, String vaClosYmOfFeeLook) {
		String vaSql = "select NVL(TO_CHAR(SUM(PC),'999,999,999.90'), 0) AS PC"
				+ " FROM(SELECT commission_negative.CLOS_YM, commission_negative.PE_NO_COL,commission_negative.PO_NO,commission_negative.AFYP, commission_negative.ARYP2,"
				+ " sum( commission_negative.AFYP+commission_negative.ARYP2) AS PC "
				+ " FROM tsli_sfc_commission_negative  commission_negative "
				+ " WHERE commission_negative.pe_no_mm = ? AND commission_negative.clos_ym = ?  "
				+ " GROUP BY commission_negative.PE_NO_MM, commission_negative.PE_NO_COL, commission_negative.PO_NO , commission_negative.CLOS_YM,"
				+ " commission_negative.AFYP, commission_negative.ARYP2) ";
			Map<String, Object> result = db.querySingle(vaSql,vaPeNoMM,vaClosYmOfFeeLook);
			if (result != null) {
				return result.get("PC").toString().trim();
			} else {
				return null;
			}
	}
	 
	          
}
